//
//  BookTable.swift
//  BookStore
//
//  Created by JinYuXin on 2022/5/11.
//

import Foundation

class BookTable{
    static var bookModels = [Book]()
    static func initTable(){
        let sqlite = SQLiteManager.sharedInstance
        if !sqlite.openDB(){return}
        let createSql = "CREATE TABLE IF NOT EXISTS book('id' TEXT NOT NULL PRIMARY KEY, " + "'title' TEXT,'kind' TEXT,'price' TEXT,'author' TEXT,'code' TEXT,'description' TEXT,'pic' TEXT);"
        if !sqlite.execNoneQuerySQL(sql: createSql){sqlite.closeDB() ; return}
        sqlite.closeDB()
        
    }

    static func InsertSingleBook(book:Book){
        let sqlite = SQLiteManager.sharedInstance
        if !sqlite.openDB(){return}
        let update = "INSERT OR REPLACE INTO book(id,price,title,kind,pic,author,code,description) VALUES('"+book.id+"','"+book.price+"','"+book.title+"','"+book.kind+"','"+book.pic+"','"+book.author+"','"+book.code+"','"+book.description+"');"
        
        if !sqlite.execNoneQuerySQL(sql: update){sqlite.closeDB() ; return}
        
        sqlite.closeDB()
    }
    
    static func insertAllBookFromWeb()  {
        let url = URL(string: "http://zy.whu.edu.cn/cs/api/book/list")!
        var booklist:[[String:String]] = [["":""]]
        let task = URLSession.shared.dataTask(with: url){
            (data,response,error) in
            if let data = data,
               let jsonobj = try? JSONSerialization.jsonObject(with: data, options: .mutableLeaves),
               let jsn = jsonobj as? [String:Any]
               {
                booklist = jsn["result"] as! [[String:String]]
               // print(booklist)
                for book in booklist
                {
                    let bookobj = Book(id:book["id"]!,price:book["price"]!,title:book["title"]!,kind:book["kind"]!,pic:book["pic"]!,author:book["author"]!,code:book["code"]!,description:book["description"]!)
                    bookModels.append(bookobj)
                    
                }
                for book in bookModels
                {
                    InsertSingleBook(book: book)
                }
            }
        }
        task.resume()
    }
    static func GetBook(){
        
        let sqlite = SQLiteManager.sharedInstance
        
        if !sqlite.openDB(){return}
        
        let queryResult = sqlite.execQuerySQL(sql: "SELECT * FROM book")
 
        sqlite.closeDB() ;
    }
    static func getKind() -> [[String:Any]]?
    {
        let sqlite = SQLiteManager.sharedInstance
        sqlite.openDB()
        let queryResult = sqlite.execQuerySQL(sql: "SELECT DISTINCT kind FROM book;")
        sqlite.closeDB();
        return queryResult
    }
    static func getBook(_ kind:String) -> [[String:Any]]?
    {
        let sqlite = SQLiteManager.sharedInstance
        sqlite.openDB()
        let queryResult = sqlite.execQuerySQL(sql: "SELECT id,title,pic,price FROM book WHERE kind='"+kind+"';")
        sqlite.closeDB();
        return queryResult
    }
    static func getBookDEtail(_ bookid:String) -> [[String:Any]]?
    {
        let sqlite = SQLiteManager.sharedInstance
        sqlite.openDB()

        let queryResult = sqlite.execQuerySQL(sql: "SELECT title,price,pic,author,kind,description,code FROM book WHERE id='"+bookid+"';")
        sqlite.closeDB();
        return  queryResult
    }
    static func getBookInfo(_ book:String) -> [[String:Any]]?
    {
        let sqlite = SQLiteManager.sharedInstance
        sqlite.openDB()

        let queryResult = sqlite.execQuerySQL(sql: "SELECT title,pic,price FROM book WHERE id='"+book+"';")
    
        sqlite.closeDB();
        return queryResult
    }
}
